﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;

namespace Vacabulary
{
    internal class DBHelper
    {
        private static string connectionString = "Data Source=vocabulary.db;Version=3;";

        // 初始化数据库
        public static void InitializeDatabase()
        {
            if (!File.Exists("vocabulary.db"))
            {
                SQLiteConnection.CreateFile("vocabulary.db");
                using (var conn = new SQLiteConnection(connectionString))
                {
                    conn.Open();
                    string sql = @"CREATE TABLE Words (
                                ID INTEGER PRIMARY KEY AUTOINCREMENT,
                                English TEXT NOT NULL,
                                Chinese TEXT NOT NULL);";
                    new SQLiteCommand(sql, conn).ExecuteNonQuery();

                    // 添加示例数据
                    InsertWord("hello", "你好");
                    InsertWord("computer", "计算机");
                    InsertWord("programming", "编程");
                    InsertWord("world","世界");
                    InsertWord("simultaneously", "同时");
                    InsertWord("kick", "踢");
                }
            }
        }

        // 插入单词
        public static void InsertWord(string english, string chinese)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string sql = "INSERT INTO Words (English, Chinese) VALUES (@eng, @chn)";
                var cmd = new SQLiteCommand(sql, conn);
                cmd.Parameters.AddWithValue("@eng", english);
                cmd.Parameters.AddWithValue("@chn", chinese);
                cmd.ExecuteNonQuery();
            }
        }

        // 随机获取单词
        public static (string English, string Chinese) GetRandomWord()
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string sql = "SELECT English, Chinese FROM Words ORDER BY RANDOM() LIMIT 1";
                using (var cmd = new SQLiteCommand(sql, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return (reader["English"].ToString(),
                                    reader["Chinese"].ToString());
                        }
                    }
                }
            }
            return (null, null);
        }
    }
}
